﻿using System;
using System.Collections.Generic;
using System.Collections.Concurrent;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace BTS6000
{
    internal class SQL
    {
        static public SQL Instance;

        private string InitialCatalog="Testpad";

        private SqlConnection Connection;

        private readonly Dictionary<string, string> ColumnBook = new Dictionary<string, string>();
        private Dictionary<string, string> ValueBook = new Dictionary<string, string>();
        private ConcurrentQueue<string> Queue = new ConcurrentQueue<string>();
        private int _Inserted = 0;

        public ConnectionState State { get { return Connection.State; } }

        public int Inserted { get { return _Inserted; } }

        public int Buffered { get { return Queue.Count; } }

        public SQL()
        {
            #region Columns

            ColumnBook.Add("N02", "Device");
            ColumnBook.Add("N0C", "Firmware");
            ColumnBook.Add("N0D", "Model");
            ColumnBook.Add("N03", "Barcode");
            ColumnBook.Add("N13", "CellBarcode");
            ColumnBook.Add("N04", "Datetime");
            ColumnBook.Add("N00", "Conclusion");
            ColumnBook.Add("N0A", "ErrorCode");
            ColumnBook.Add("N05", "Temperature");
            ColumnBook.Add("D00", "OCV1");
            ColumnBook.Add("D01", "OCV2");
            ColumnBook.Add("D02", "IR");
            ColumnBook.Add("D03", "R1");
            ColumnBook.Add("D04", "R1T");
            ColumnBook.Add("D05", "R2");
            ColumnBook.Add("D06", "R2C");
            ColumnBook.Add("D21", "R2R");
            ColumnBook.Add("D09", "DSGV");
            ColumnBook.Add("D0A", "DSGC");
            ColumnBook.Add("D0B", "DCIR");
            ColumnBook.Add("D0E", "DOT");
            ColumnBook.Add("D0F", "DOC");
            ColumnBook.Add("D1E", "DOT1");
            ColumnBook.Add("D12", "ST");
            ColumnBook.Add("D13", "SV");
            ColumnBook.Add("D14", "SRV");
            ColumnBook.Add("D0C", "CHGV");
            ColumnBook.Add("D0D", "CHGC");
            ColumnBook.Add("D10", "COT");
            ColumnBook.Add("D11", "COC");
            ColumnBook.Add("D1F", "CHG_Leakage");
            ColumnBook.Add("D20", "DSG_Leakage");
            ColumnBook.Add("D23", "SDA_VOL");
            ColumnBook.Add("D24", "SCL_VOL");
            ColumnBook.Add("D22", "HDQ_VOL");
            ColumnBook.Add("D25", "V1_VOL");
            ColumnBook.Add("D26", "CELL_VOL");
            ColumnBook.Add("D27", "HIR_AIDED");
            ColumnBook.Add("D28", "HIR_RES");
            ColumnBook.Add("D29", "HIR_VOL");
            ColumnBook.Add("P00", "DS_EP0");
            ColumnBook.Add("P01", "DS_EP1");
            ColumnBook.Add("P02", "DS_EP2");
            ColumnBook.Add("P03", "DS_EP3");
            ColumnBook.Add("P04", "DS_EP4");
            ColumnBook.Add("P05", "DS_EP5");
            ColumnBook.Add("P06", "DS_EP6");
            ColumnBook.Add("P07", "DS_EP7");
            ColumnBook.Add("PF0", "DS_DS_Family");
            ColumnBook.Add("PF1", "DS_ROM");

            #endregion

            Task_Flushing();

            Instance = this;
        }

        public bool Connect(string dataSource, string initialCatalog, string userID, string password,bool local)
        {
            if(string.IsNullOrEmpty(dataSource) || string.IsNullOrEmpty(initialCatalog) || string.IsNullOrEmpty(userID))
            {
                return false;
            }
            else
            {
                SqlConnectionStringBuilder sb = new SqlConnectionStringBuilder();

                sb.DataSource = dataSource;
                sb.InitialCatalog = initialCatalog;
                sb.UserID = userID;
                sb.Password = password;
                sb.IntegratedSecurity = local;

                if ((Connection != null) && (Connection.State == ConnectionState.Open))
                {
                    Connection.Close();
                }

                Connection = new SqlConnection(sb.ConnectionString);

                try
                {
                    Connection.Open();
                }
                catch(Exception ex)
                {
                    Console.WriteLine(ex.ToString());
                }

                InitialCatalog=initialCatalog;

                return (Connection.State == ConnectionState.Open);
            }
        }

        public void Start()
        {
            ValueBook.Clear();
        }

        public void AppendWithKey(string key,string value)
        {
            if(ColumnBook.ContainsKey(key))
            {
                AppendWithName(ColumnBook[key], value);
            }
        }

        public void AppendWithName(string name, string value)
        {
            if (ValueBook.ContainsKey(name))
            {
                ValueBook[name] = value;
            }
            else
            {
                ValueBook.Add(name, value);
            }
        }

        public void Flush(string tableName)
        {
            StringBuilder InsertBuilder = new StringBuilder(1024);
            StringBuilder Columns = new StringBuilder(1024);
            StringBuilder Values = new StringBuilder(1024);
            SqlCommand Cmd = new SqlCommand();

            foreach (var v in ValueBook)
            {
                Columns.AppendFormat("[{0}],", v.Key);
                Values.AppendFormat("'{0}',", v.Value);
            }

            Columns.AppendFormat("[{0}]", "Datetime");
            Values.AppendFormat("{0}", "getdate()");

            InsertBuilder.AppendFormat("USE {0};\r\n ", InitialCatalog);
            InsertBuilder.Append("INSERT INTO");
            InsertBuilder.AppendFormat(" {0} ", tableName);
            InsertBuilder.Append("(");
            InsertBuilder.Append(Columns.ToString());
            InsertBuilder.Append(") VALUES (");
            InsertBuilder.Append(Values.ToString());
            InsertBuilder.Append(")");

            if(Connection!=null)
            {
                Queue.Enqueue(InsertBuilder.ToString());
            }
        }

        public bool AppendParameter(string tableName,string model,string note,string para)
        {
            StringBuilder InsertBuilder = new StringBuilder(1024);
            SqlCommand Cmd;

            InsertBuilder.AppendFormat("USE {0};\r\n ", InitialCatalog);
            InsertBuilder.Append("INSERT INTO");
            InsertBuilder.AppendFormat(" {0} ", tableName);

            InsertBuilder.Append("(");
            InsertBuilder.Append("[Model],[Datetime],[Note],[Parameter]");
            InsertBuilder.Append(") VALUES (");
            InsertBuilder.AppendFormat("'{0}',getdate(),'{1}','{2}'",model,note,para);
            InsertBuilder.Append(")");

            Cmd = new SqlCommand(InsertBuilder.ToString(), Connection);

            return (Cmd.ExecuteNonQuery() > 0);
        }

        public DataTable Select(string tableName,string columns,string conditions)
        {
            StringBuilder CmdString = new StringBuilder(1024);
            DataTable Table = new DataTable();

            CmdString.AppendFormat("USE {0};\r\n ", InitialCatalog);
            CmdString.AppendFormat("SELECT {0} FROM {1} WHERE ({2})", columns, tableName, conditions);

            if ((Connection != null) && (Connection.State == ConnectionState.Open))
            {
                SqlDataAdapter Adapter = new SqlDataAdapter(CmdString.ToString(), Connection);

                Clipboard.SetText(CmdString.ToString());

                try
                {
                    Adapter.Fill(Table);                    
                }
                catch (Exception ex)
                {
                    MessageBox.Show(CmdString.ToString() + "\r\n" + ex.Message, "Select", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }

            return Table;
        }

        private void Task_Flushing()
        {
            System.Threading.Tasks.Task.Factory.StartNew
            (
                () =>
                {
                    #region Task

                    while(true)
                    {
                        if (Connection == null)
                        {
                            System.Threading.Thread.Sleep(100);
                        }
                        else
                        {
                             if(Connection.State != ConnectionState.Open)
                            {
                                System.Threading.Thread.Sleep(100);
                                if (Connection.State != ConnectionState.Open)
                                {
                                    try
                                    {
                                        Connection.Open();
                                    }
                                    catch
                                    {
                                    }
                                }
                            }
                            else if (Queue.IsEmpty)
                            {
                                System.Threading.Thread.Sleep(100);
                            }
                            else
                            {
                                string InsertString;

                                if(Queue.TryDequeue(out InsertString))
                                {
                                    try
                                    {
                                        SqlCommand Cmd = new SqlCommand(InsertString, Connection);
                                        if(Cmd.ExecuteNonQuery()>0)
                                        {
                                            System.Threading.Interlocked.Increment(ref _Inserted);
                                        }
                                        else
                                        {
                                            //插入失败时,重新放回缓冲区
                                            Queue.Enqueue(InsertString);
                                        }
                                    }
                                    catch
                                    {
                                        //插入失败时,重新放回缓冲区
                                        Queue.Enqueue(InsertString);
                                    }
                                }
                            }
                        }
                    }

                    #endregion
                }
            );
        }
    }
}
